The Field Object

The Recordset object exposes the Fields collection, which in turn contains one or more Field objects. Each Field object represents one column in the data source and exposes 12 properties and 2 methods.

The properties of a Field object can be divided into two distinct groups: properties that describe the attributes and the characteristics of the field (and that are available also when the Recordset is closed) and properties that describe the contents of a field in the current record. (These properties are available only when the Recordset is open and the current record isn't invalid.)

All the properties that describe the Field object's characteristics (which are also known as metadata properties) are read/write if you're adding the Field object to a stand-alone Recordset and read-only after the Recordset has been opened.

The Name property is the name of the database column the Field object takes data from and writes data to. Because this property is also the key associated with the Field object in the Fields collection, you can refer to a particular field in one of three ways, using the following syntax:

' Full syntax rs.Fields("LastName").Value = "Smith" ' Fields is the Recordset's default property. rs("LastName").Value = "Smith" ' Value is the Field's default property. rs("LastName") = "Smith"

You usually enumerate the fields in a Recordset using a For…Next or For Each…Next loop:

For i = 0 To rs.Fields.Count _ 1 lstFieldNames.AddItem rs.Fields(i).Name Next

The Type property returns an enumerated constant that defines which kind of values can be stored in the field. All the types that ADO supports are listed in Table 13-5, but you should be aware that not all OLE DB providers and database engines support all these data types. The Type property also indirectly affects NumericScale, Precision, and DefinedSize.

Table 13-5. The constants used for the Type property of the Field, Parameter, and Property objects.

Constant	Value	Description
adEmpty	0	No value specified
adSmallInt	2	2-byte signed integer
adInteger	3	4-byte signed integer
adSingle	4	Single-precision floating point value
adDouble	5	Double-precision floating point value
adCurrency	6	Currency value
adDate	7	Date value (stored in a Double value, in the same format as Visual Basic's Date variables)
adBSTR	8	Null-terminated Unicode string
adIDispatch	9	Pointer to an IDispatch interface of an OLE object
adError	10	32-bit error code
adBoolean	11	Boolean value
adVariant	12	Variant value
adIUnknown	13	Pointer to an IUnknown interface of an OLE object
adDecimal	14	Numeric value with fixed precision and scale
adTinyInt	16	1-byte signed integer
adUnsignedTinyInt	17	1-byte unsigned integer
adUnsignedSmallInt	18	2-byte unsigned integer
adUnsignedInt	19	4-byte unsigned integer
adBigInt	20	8-byte signed integer
adUnsignedBigInt	21	8-byte unsigned integer
adGUID	72	Globally Unique Identifier (GUID)
adBinary	128	Binary value
adChar	129	String value
adWChar	130	Null-terminated Unicode character string
adNumeric	131	Exact numeric value with fixed precision and scale
adUserDefined	132	User-defined variable
adDBDate	133	Date value in format "yyyymmdd"
adDBTime	134	Time value in format "hhmmss"
adDBTimeStamp	135	Date and time stamp in format "yyyymmddhhmmss" plus a fraction in billionths
adChapter	136	Chapter (a dependent Recordset in a hierarchical Recordset)
adVarNumeric	139	Variable-length exact numeric value with fixed precision and scale
adVarChar	200	String value (Parameter object only)
adLongVarChar	201	Long variable-length character string (Parameter object only)
adVarWChar	202	Null-terminated Unicode character string (Parameter object only)
adLongVarWChar	203	Long variable-length Unicode character string (Parameter object only)
adVarBinary	204	Binary value (Parameter object only)
adLongVarBinary	205	Long variable-length binary data (Parameter object only)

The DefinedSize property returns the maximum capacity that was defined when the field was created. The NumericScale property indicates the scale of numeric values (in other words, the number of digits to the right of the decimal point that will be used to represent the value). The Precision property is the degree of precision for numeric values in a numeric Field object (that is, the maximum total number of digits used to represent the value). The Attributes property is a bit-field value that returns information about the field. It can contain one or more of the constants listed in Table 13-6.

Table 13-6. Constants used for the Attributes property of the Field object.

Constant	Value	Description
adFldMayDefer	2	A deferred field—that is, a field whose value is retrieved only when the field is explicitly referenced in the code. BLOB and CLOB fields are often fields of this type.
adFldUpdatable	4	The field is updatable.
adFldUnknownUpdatable	8	The provider can't determine whether the field is writable.
adFldFixed	&H10	The field contains fixed-length data.
adFldIsNullable	&H20	The field accepts Null values.
adFldMayBeNull	&H40	The field can contain Null values (but doesn't necessarily accept them).
adFldLong	&H80	The field is a long binary field (for example, a BLOB or a CLOB), and you can use the AppendChunk and GetChunk methods on it.
adFldRowID	&H100	The field contains a record identifier that can't be written to and that has no meaningful value except for identifying the row (for example, a record number or a unique identifier).
adFldRowVersion	&H200	The field contains some kind of date stamp or time stamp that is used to track record updates.
adFldCacheDeferred	&H1000	The field is cached the first time it is read from the database, and all subsequent reads fetch the data from the cache.
adFldKeyColumn	&H8000	The field is part of the key.

The Value property sets or returns the contents of the field. It is also the default property of the Field object, so you can omit it if you want to:

rs.Fields("BirthDate") = #4/12/1955#

The ActualSize property is a read-only property that returns the number of bytes taken by the current value in the field. Don't confuse this property with the DefinedSize property, which returns the declared maximum length of the field. This property is especially useful with BLOB and CLOB fields. If the provider can't determine the size of the field, it returns that value -1. (The Visual Basic documentation states that in this case this property returns the constant adUnknown; however, this constant doesn't appear in the ADODB type library.)

The OriginalValue property returns the value that was in the field before any changes were made. If you're in immediate update mode, this is the value that the CancelUpdate method uses to restore the field's contents; if you're in batch update mode, this is the value that was valid after the last UpdateBatch method and is also the value that the CancelBatch method uses to restore the field's contents.

The UnderlyingValue property is the value that is currently stored in the database. This value might be different from the OriginalValue property if another user has updated the field since you last read it. A Resync method would assign this value to the Value property. You typically use this property together with the OriginalValue property to resolve conflicts arising from batch updates.

You can assign the DataFormat property a StdDataFormat object so that you can control how values coming from the data source are formatted in the field. For more information on this property, see the section, "The DataFormat Property" in Chapter 8.

The Field object supports only two methods, both of which are used only with large binary fields such as BLOB or CLOB fields. (These are the fields whose Attributes property has the adFldLong bit set.) Because these fields can be several kilobytes—or even hundreds of kilobytes—long, writing to them and reading them back in smaller chunks is often more practical.

The AppendChunk method writes a chunk of data to a Field and expects a Variant argument that contains the data to be written. Usually, you write the contents of a file in chunks of 8 KB or 16 KB, and in most cases, you want to store a large amount of data that you have in a file, such as a long document or a bitmap. Here's a reusable routine that moves the contents of a file into a field that supports the AppendChunk method:

Sub FileToBlob(fld As ADODB.Field, FileName As String, _ Optional ChunkSize As Long = 8192) Dim fnum As Integer, bytesLeft As Long, bytes As Long Dim tmp() As Byte ' Raise an error if the field doesn't support GetChunk. If (fld.Attributes And adFldLong) = 0 Then Err.Raise 1001, , "Field doesn't support the GetChunk method." End If ' Open the file; raise an error if the file doesn't exist. If Dir$(FileName) = " " Then Err.Raise 53, ,#"File not found" fnum = FreeFile Open FileName For Binary As fnum ' Read the file in chunks, and append data to the field. bytesLeft = LOF(fnum) Do While bytesLeft bytes = bytesLeft If bytes > ChunkSize Then bytes = ChunkSize ReDim tmp(1 To bytes) As Byte Get #1, , tmp fld.AppendChunk tmp bytesLeft = bytesLeft - bytes Loop Close #fnum End Sub

The first time you call this method for a given field, it overwrites the current contents of the field; each subsequent call to this method simply appends data to the current value of the field. If you read or write another field in the record and then go back and restart appending data with the AppendChunk method, ADO assumes that you're appending a brand new value and overwrites the field's contents. ADO also overwrites the contents of the field when you start to work with another field in a Recordset clone, but not when you work with a field in another Recordset that isn't a clone of the current one.

You can use the GetChunk method to read back the data stored in a Field that contains a long binary value. This method takes one argument: the number of bytes that must be read from the Field object. The problem with this method is that if you read too many bytes, ADO will pad the returned string with spaces. Such spaces are usually something you don't want to retrieve, especially when you're working with images or other binary data. For this reason, you should test the ActualSize property to ensure that you don't read more bytes than necessary. I've prepared a reusable routine that does this testing for you automatically:

Sub BlobToFile(fld As ADODB.Field, FileName As String, _ Optional ChunkSize As Long = 8192) Dim fnum As Integer, bytesLeft As Long, bytes As Long Dim tmp() As Byte ' Raise an error if the field doesn't support GetChunk. If (fld.Attributes And adFldLong) = 0 Then Err.Raise 1001, , "Field doesn't support the GetChunk method." End If ' Delete the file if it exists already, and then open a new one for writing. If Dir$(FileName) <> "" Then Kill FileName fnum = FreeFile Open FileName For Binary As fnum ' Read the field's contents, and write the data to the file ' chunk by chunk. bytesLeft = fld.ActualSize Do While bytesLeft bytes = bytesLeft If bytes > ChunkSize Then bytes = ChunkSize tmp = fld.GetChunk(bytes) Put #fnum, , tmp bytesLeft = bytesLeft - bytes Loop Close #fnum End Sub

Multiple GetChunks methods continue to retrieve data starting from where the previous GetChunk method left off. But if you read or write the value of another field in the same Recordset (or in a clone of the Recordset), the next time you execute a GetChunk method on the original field ADO will restart from the beginning of the field. Also, remember that BLOB fields should be the last fields in SELECT queries against SQL Server data sources.

You can use the Fields collection in two distinct ways. The simplest and most intuitive way is by iterating on its items to retrieve information about the fields of a Recordset—for example, when you want to create a list of field names and values:

' Error trapping accounts for values, such as BLOB fields, that ' can't be converted to strings. On Error Resume Next For i = 0 To rs.Fields.Count - 1 lstFields.AddItem rs.Fields(i).Name & " = " & rs.Fields(i).Value Next

The Fields collection also supports the Append method, which creates a new Field object and appends it to the collection. This method is useful when you want to manufacture a Recordset object in memory without necessarily connecting it to a data source (not immediately at least). You can use this method only with client-side Recordsets (CursorLocation = adUseClient) and only if the Recordset is closed and isn't currently associated with a Connection (ActiveConnection = Nothing). The Append method has the following syntax:

Append(Name, Type, [DefinedSize], [Attrib]) As Field

The arguments define the properties of the Field object being created. The following reusable routine creates a new stand-alone Recordset that has the same field structure of another Recordset:

Function CopyFields(rs As ADODB.Recordset) As ADODB.Recordset Dim newRS As New ADODB.Recordset, fld As ADODB.Field For Each fld In rs.Fields newRS.Fields.Append fld.Name, fld.Type, fld.DefinedSize, _ fld.Attributes Next Set CopyFields = newRS End Function

Here's another routine that creates a new stand-alone record that not only duplicates the field structure of an existing Recordset but also duplicates all the records that it contains (but without being a clone Recordset):

Function CopyRecordset(rs As ADODB.Recordset) As ADODB.Recordset Dim newRS As New ADODB.Recordset, fld As ADODB.Field Set newRS = CopyFields(rs) newRS.Open ' You must open the Recordset before adding new records. rs.MoveFirst Do Until rs.EOF newRS.AddNew ' Add a new record. For Each fld In rs.Fields ' Copy all fields' values. newRS(fld.Name) = fld.Value ' Assumes no BLOB fields Next rs.MoveNext Loop Set CopyRecordset = newRS End Function

The Fields collection also supports the Delete method, which removes a field in a stand-alone record before opening it, and the Refresh method.